Exploration

1 Packages, Data and Custom Functions

Custom Functions: This code chunk contains custom fucntions that I use to help automate or make tasks easier.

LoadPackages <- function(packages) {
  # Load or install packages if they aren't already loaded.
  #
  # Args:
  #   packages: a vector of package names
  #
  for (package in packages) {
    if (!require(package, character.only=T, quietly=T)) {
      if (!package %in% installed.packages()) install.packages(package)
      library(package, character.only=T)
    }
  }
}
ColNa <- function(df) {
  # Return a vector of column names that have NA values
  #
  # Args:
  # df: data frame 
  #
  # Returns:
  #  vector of column names that have NAs 
  df <- tbl_df(df)
  v <- c()
  j <- 1
  for(i in 1:ncol(df)) {
    x <- df[ ,i]
    xRmvNA <- x[!is.na(x),]
    lenx <- nrow(x)
    lenxRmvNA <- nrow(xRmvNA)
    if(lenx != lenxRmvNA ) {
      v[j] <- colnames(df[ ,i])
      j <- j+1
    }
  }
  return(v)
}

Load Packages: Packages contain functions that others write that help with doing stuff

# I use my custom function LoadPackages to load or install packages
LoadPackages(c("dplyr", "haven", "stringr", "tidyr", "ggplot2"))

Read in data

data_path <- 'E:/Tommy/Original_Data/Data'
# The list.files function gets the file names in a specified directory
# file_names contains the file names of all the data sets
file_names <- list.files(path = data_path, recursive = TRUE)
# Initialize a vector to hold data frame names
df_names <- c()
i <- 1
# This loop goes through the file names and reads it into R
for(name in file_names) {
  # create variable name for df
  df_name <- str_replace_all(name, " |_with_ann2|.sas7bdat|/acs_16_5yr|_records", "")
  # read in data
  df <- read_sas(paste0(data_path, "/", name))
  # attach df to variable name
  assign(df_name, df) 
  print(paste0("done with: ", df_name, " ", i,"/",length(file_names)))
  i <- i+1
  # add the current iteration of the data frame name to the df_names vector
  df_names <- c(df_names, df_name)
}

2 Data Descritpions

DF names for the data that was read in.
- There are 27 sets of demographics data. 9 simliar sets for each state (MA NC OR)
- The data sets that start with copy are exact copies of other datasets and can be ignored
- The medication_details dataset has some info about the drug (ex. price, manufacture etc.)
- rx_nqty: quantity of NEW opiod pills for each month by zip code
- rx_nrx: number of NEW opiod prescriptions for each month by zip code
- rx_tqty: quantity of TOTAL opiod pills for each month by zip code
- rx_trx: number of TOTAL opiod prescriptions for each month by zip code

df_names %>% as.data.frame()

The medication_details dataset has some info about the drug (ex. price, manufacture etc.)

medication_details

rx_nqty: quantity of NEW opiod pills for each month by zip code

rx_nqty

rx_nrx: number of NEW opiod prescriptions for each month by zip code

rx_nrx

rx_tqty: quantity of TOTAL opiod pills for each month by zip code

rx_tqty

rx_trx: number of TOTAL opiod prescriptions for each month by zip code

rx_trx

MA_s0101: Age and sex demographics data for each zip code in massachusetts
Column HC01_EST_VC01: is total population for the respective zip code
Column HC02_EST_VC01: total male population for the respective zip code
Column HC03_EST_VC01: total femal population for respective zip code
- You can view the data dictionary to find out what the other columns mean
- The other demographics data sets likely have a very simialr format

MA_s0101

Check that the files named with copy really just are duplicates

rx_nqty %>%
  all_equal(Copyofrx_nqty)
Copyofrx_nrx %>%
  all_equal(rx_nrx)
Copyofrx_tqty %>%
  all_equal(Copyofrx_tqty)
Copyofrx_trx %>%
  all_equal(rx_trx)

3 Total Rx By State

Prepare data to see total rx by state for each month

rx_trx_t <- rx_trx %>% select(ST, starts_with("TRx"))
rx_trx_t <- rx_trx %>%
  select(ST, starts_with("TRx")) %>%
  group_by(ST) %>% 
  summarise_all(funs(sum(.))) %>% 
  ungroup() %>% 
  gather(key = "date", value = TRx, -ST) %>%
  mutate(date = str_replace_all(date, "TRx_", "")) %>% 
  mutate(date = as.Date(date, '%m%d%Y')) %>%
  mutate(pop = ifelse(ST == 'MA', 6.86, 
                      ifelse(ST == "OR", 4.143, 10.27)),
         rx_pop_ratio = TRx / pop)
# prepare data to see difference between total rx for NC and MA
MA_rx_pop <- rx_trx_t %>% 
  filter(ST == "MA") %>% 
  mutate(MA_rx = rx_pop_ratio) %>% 
  select(MA_rx)
OR_rx_pop <- rx_trx_t %>% 
  filter(ST == "OR") %>% 
  mutate(OR_rx = rx_pop_ratio) %>% 
  select(OR_rx)
NC_rx_pop <- rx_trx_t %>% 
  filter(ST == "NC") %>% 
  mutate(NC_rx = rx_pop_ratio) %>% 
  select(NC_rx)
diff_df <- bind_cols(rx_trx_t %>% distinct(date), MA_rx_pop, OR_rx_pop, NC_rx_pop) %>%
  mutate(NC_MA_diff = NC_rx - MA_rx)

Line plot of total rx by state for each month

ggplot(data = rx_trx_t, aes(x = date, y = TRx, color = ST)) +
  geom_line() +
  ggtitle("Total Rx")

ggplot(data = rx_trx_t, aes(x = date, y = rx_pop_ratio, color = ST)) +
  geom_line() +
  ggtitle("Total Rx per Million Residents")

ggplot(data = diff_df, aes(x = date, y = NC_MA_diff)) +
  geom_line() +
  ggtitle("Diff Total Rx per Million Residents Between NC and MA")

Bar Chart of Total Scripts for each State

4 MISC (scratch / junk).

foo <- rx_trx %>% group_by(city, ST, Zip) %>% summarise(sum(TRx_08312017))

# cast to numeric data type
MA_s0101_d <- MA_s0101 %>% mutate_all(funs(as.double(.)))

NAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercion

# get col names that have NA values
na <- ColNa(MA_s0101_d)
# get all col names
all <- names(MA_s0101)
# this  is column names that don't have any NA values
setdiff(all,na)

[1] "Zip"           "HC01_EST_VC01" "HC02_EST_VC01" "HC03_EST_VC01"

Write datesets out as csv files

for (name in df_names) {
  write.csv(get(name), paste0('E:/Tommy/scratch/csv/',name,".csv"))
}